-- Joining More Than Two Tables
SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinesEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name



-- Joining a Table to Itself
SELECT DISTINCT pv1.ProductID, pv1.BusinessEntityID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
    AND pv1.BusinessEntityID <> pv2.BusinessEntityID
ORDER BY pv1.ProductID



-- Joining a Table using a Non-Equi Join
SELECT DISTINCT p1.BusinessEntityID, p1.ProductID
FROM Purchasing.ProductVendor p1
    INNER JOIN Purchasing.ProductVendor p2
    ON p1.ProductID = p2.ProductID
WHERE p1.BusinessEntitytID <> p2.BusinessEntityID
ORDER BY p1.BusinessEntityID
